from typing import Optional
from fastapi import APIRouter, Depends, Query
from pydantic import BaseModel, Field
from sqlalchemy import text
from sqlalchemy.orm import Session

from commons.BaseResponse import BaseResponse
from commons.PaginationBase import PaginationBase
from datebase import get_db
from routers.dangerRouter.mysafe03 import data_my_safe_03
from utils.EncryptionUtil import oauth2_scheme

data_my_safe_04 = APIRouter(prefix="/phone", tags=["储物柜管理"], dependencies=[Depends(oauth2_scheme)])


class CabinetAlarmSearch(PaginationBase):
    # 筛选条件
    fsiteNo: str = Field("", description="站点编号")
    fbegdate: str = Field("2000-01-01", description="开始日期(yyyy-MM-dd)，默认'2000-01-01'")
    fenddate: str = Field("2000-01-01", description="结束日期(yyyy-MM-dd)，默认'2000-01-01'")
    fpointNo: str = Field("", description="点位编号")
    boxFequipmentbarcode: str = Field("", description="箱设备条码")
    cabFequipmentbarcode: str = Field("", description="柜设备条码")
    ftype: str = Field("101", description="报警类型编码，默认'101'")


@data_my_safe_04.get("/getHardwareAlarm", summary="储物柜-操作报警+硬件报警+风扇报警查询接口")
def get_hardware_alarm(cabinet: CabinetAlarmSearch = Query(), db: Session = Depends(get_db)):
    param = {}
    sql = "select count(1) from i_tc_hardwarewarring a"
    sql = sql + " left join i_ts_box b on a.fconbarcode = b.fequipmentbarcode"
    sql = sql + " left join lims.i_ts_cabinet c on c.fequipmentbarcode = a.fcabbarcode "
    sql = sql + " left join lims.i_ts_warehouse d on d.fequipmentbarcode = a.fboxbarcode"
    sql = sql + " left join s_ts_dict e on e.fdicttype = 3010 and e.fdictno = cast(a.ftype as char)"
    sql = sql + " left join a_ts_point s on b.fpoint_no = s.fpoint_no"
    sql = sql + " left join a_ts_site t on s.fsite_no = t.fsite_no"
    sql = sql + " where 1=1"
    sql = sql + " and to_char(a.fusedate,'yyyymmdd') between :ftakedateStart  and :ftakedateEnd "
    param["ftakedateStart"] = cabinet.fbegdate
    param["ftakedateEnd"] = cabinet.fenddate
    sql = sql + " and a.ftype= :ftype "
    param["ftype"] = cabinet.ftype
    if cabinet.fsiteNo != "":
        sql = sql + " and t.fsite_no= :fsiteNo "
        param["fsiteNo"] = cabinet.fsiteNo
    if cabinet.fpointNo != "":
        sql = sql + " and s.fpoint_no= :fpointNo "
        param["fpointNo"] = cabinet.fpointNo
    if cabinet.boxFequipmentbarcode != "":
        sql = sql + " and a.fboxbarcode= :boxFequipmentbarcode "
        param["boxFequipmentbarcode"] = cabinet.boxFequipmentbarcode
    if cabinet.cabFequipmentbarcode != "":
        sql = sql + " and a.fcabbarcode= :cabFequipmentbarcode "
        param["cabFequipmentbarcode"] = cabinet.cabFequipmentbarcode
    count = db.execute(text(sql), param).scalar()

    param = {}
    sql = "select to_char(a.fusedate,'yyyy-mm-dd hh24:mi:ss') fusedate,t.fsite_no,COALESCE(t.fsite_name,'') fsite_name"
    sql = sql + " ,COALESCE(s.fpoint_name,'') fpoint_name,COALESCE(b.fequipmenttag, '') fconname"
    sql = sql + " ,COALESCE(c.fequipmenttag, '') fcabname  ,COALESCE(d.fequipmenttag, '') fboxname"
    sql = sql + " , to_char(a.fbegdate, 'HH24:MI:SS') fbegtime"
    sql = sql + " , to_char(a.fenddate, 'HH24:MI:SS') fendtime , e.fdictname  , a.fmessage"
    sql = sql + " from i_tc_hardwarewarring a"
    sql = sql + " left join i_ts_box b on a.fconbarcode = b.fequipmentbarcode"
    sql = sql + " left join lims.i_ts_cabinet c on c.fequipmentbarcode = a.fcabbarcode "
    sql = sql + " left join lims.i_ts_warehouse d on d.fequipmentbarcode = a.fboxbarcode"
    sql = sql + " left join s_ts_dict e on e.fdicttype = 3010 and e.fdictno = cast(a.ftype as char)"
    sql = sql + " left join a_ts_point s on b.fpoint_no = s.fpoint_no"
    sql = sql + " left join a_ts_site t on s.fsite_no = t.fsite_no"
    sql = sql + " where 1=1"
    sql = sql + " and to_char(a.fusedate,'yyyymmdd') between :ftakedateStart  and :ftakedateEnd "
    param["ftakedateStart"] = cabinet.fbegdate
    param["ftakedateEnd"] = cabinet.fenddate
    sql = sql + " and a.ftype= :ftype "
    param["ftype"] = cabinet.ftype
    if cabinet.fsiteNo != "":
        sql = sql + " and t.fsite_no= :fsiteNo "
        param["fsiteNo"] = cabinet.fsiteNo
    if cabinet.fpointNo != "":
        sql = sql + " and s.fpoint_no= :fpointNo "
        param["fpointNo"] = cabinet.fpointNo
    if cabinet.boxFequipmentbarcode != "":
        sql = sql + " and a.fboxbarcode= :boxFequipmentbarcode "
        param["boxFequipmentbarcode"] = cabinet.boxFequipmentbarcode
    if cabinet.cabFequipmentbarcode != "":
        sql = sql + " and a.fcabbarcode= :cabFequipmentbarcode "
        param["cabFequipmentbarcode"] = cabinet.cabFequipmentbarcode
    if cabinet.order == "desc":
        sql = sql + " order by  fusedate desc "
    else:
        sql = sql + " order by fusedate "
    sql = sql + " limit :limit offset :offset "
    param["limit"] = cabinet.limit
    param["offset"] = cabinet.offset
    data = db.execute(text(sql), param).fetchall()

    data_list = []
    for key in range(len(data)):
        d1 = {}
        d1['fusedate'] = data[key][0]
        d1['fsiteNo'] = data[key][1]
        d1['fsite_name'] = data[key][2]
        d1['fpointName'] = data[key][3]
        d1['fconname'] = data[key][4]
        d1['fcabname'] = data[key][5]
        d1['fboxname'] = data[key][6]
        d1['fbegtime'] = data[key][7]
        d1['fendtime'] = data[key][8]
        d1['fdictname'] = data[key][9]
        d1['fmessage'] = data[key][10]
        data_list.append(d1)

    info_dict = {"list": data_list, "limit": cabinet.limit, "page": cabinet.pageIndex,
                 "pages": (count + cabinet.limit - 1) // cabinet.limit,
                 "total": count}
    return BaseResponse.success(data=info_dict)


class CabinetHardwareAlarmSearch(PaginationBase):
    # 筛选条件
    fsiteNo: str = Field("", description="站点编号")
    fbegdate: str = Field("2000-01-01", description="开始日期(yyyy-MM-dd)，默认'2000-01-01'")
    fenddate: str = Field("2000-01-01", description="结束日期(yyyy-MM-dd)，默认'2000-01-01'")
    fpointNo: str = Field("", description="点位编号")
    boxFequipmentbarcode: str = Field("", description="箱设备条码")
    cabFequipmentbarcode: str = Field("", description="柜设备条码")
    ftype: str = Field("101", description="报警类型编码，默认'101'")


@data_my_safe_04.get("/getHardwareAlarm3", summary="储物柜-硬件报警")
def get_hardware_alarm(cabinet: CabinetHardwareAlarmSearch = Query(), db: Session = Depends(get_db)):
    param = {}
    sql = "select count(1) from i_tc_hardwarewarring a"
    sql = sql + " left join i_ts_box b on a.fconbarcode = b.fequipmentbarcode"
    sql = sql + " left join lims.i_ts_cabinet c on c.fequipmentbarcode = a.fcabbarcode "
    sql = sql + " left join lims.i_ts_warehouse d on d.fequipmentbarcode = a.fboxbarcode"
    sql = sql + " left join s_ts_dict e on e.fdicttype = 3010 and e.fdictno = cast(a.ftype as char)"
    sql = sql + " left join a_ts_point s on b.fpoint_no = s.fpoint_no"
    sql = sql + " left join a_ts_site t on s.fsite_no = t.fsite_no"
    sql = sql + " where 1=1"
    sql = sql + " and to_char(a.fusedate,'yyyymmdd') between :ftakedateStart  and :ftakedateEnd "
    param["ftakedateStart"] = cabinet.fbegdate
    param["ftakedateEnd"] = cabinet.fenddate
    sql = sql + " and a.ftype= :ftype "
    param["ftype"] = cabinet.ftype
    if cabinet.fsiteNo != "":
        sql = sql + " and t.fsite_no= :fsiteNo "
        param["fsiteNo"] = cabinet.fsiteNo
    if cabinet.fpointNo != "":
        sql = sql + " and s.fpoint_no= :fpointNo "
        param["fpointNo"] = cabinet.fpointNo
    if cabinet.boxFequipmentbarcode != "":
        sql = sql + " and a.fboxbarcode= :boxFequipmentbarcode "
        param["boxFequipmentbarcode"] = cabinet.boxFequipmentbarcode
    if cabinet.cabFequipmentbarcode != "":
        sql = sql + " and a.fcabbarcode= :cabFequipmentbarcode "
        param["cabFequipmentbarcode"] = cabinet.cabFequipmentbarcode
    count = db.execute(text(sql), param).scalar()

    param = {}
    sql = "select to_char(a.fusedate,'yyyy-mm-dd hh24:mi:ss') fusedate,t.fsite_no,COALESCE(t.fsite_name,'') fsite_name"
    sql = sql + " ,COALESCE(s.fpoint_name,'') fpoint_name,COALESCE(b.fequipmenttag, '') fconname"
    sql = sql + " ,COALESCE(c.fequipmenttag, '') fcabname  ,COALESCE(d.fequipmenttag, '') fboxname"
    sql = sql + " , to_char(a.fbegdate, 'HH24:MI:SS') fbegtime"
    sql = sql + " , to_char(a.fenddate, 'HH24:MI:SS') fendtime , e.fdictname  , a.fmessage"
    sql = sql + " from i_tc_hardwarewarring a"
    sql = sql + " left join i_ts_box b on a.fconbarcode = b.fequipmentbarcode"
    sql = sql + " left join lims.i_ts_cabinet c on c.fequipmentbarcode = a.fcabbarcode "
    sql = sql + " left join lims.i_ts_warehouse d on d.fequipmentbarcode = a.fboxbarcode"
    sql = sql + " left join s_ts_dict e on e.fdicttype = 3010 and e.fdictno = cast(a.ftype as char)"
    sql = sql + " left join a_ts_point s on b.fpoint_no = s.fpoint_no"
    sql = sql + " left join a_ts_site t on s.fsite_no = t.fsite_no"
    sql = sql + " where 1=1"
    sql = sql + " and to_char(a.fusedate,'yyyymmdd') between :ftakedateStart  and :ftakedateEnd "
    param["ftakedateStart"] = cabinet.fbegdate
    param["ftakedateEnd"] = cabinet.fenddate
    sql = sql + " and a.ftype= :ftype "
    param["ftype"] = cabinet.ftype
    if cabinet.fsiteNo != "":
        sql = sql + " and t.fsite_no= :fsiteNo "
        param["fsiteNo"] = cabinet.fsiteNo
    if cabinet.fpointNo != "":
        sql = sql + " and s.fpoint_no= :fpointNo "
        param["fpointNo"] = cabinet.fpointNo
    if cabinet.boxFequipmentbarcode != "":
        sql = sql + " and a.fboxbarcode= :boxFequipmentbarcode "
        param["boxFequipmentbarcode"] = cabinet.boxFequipmentbarcode
    if cabinet.cabFequipmentbarcode != "":
        sql = sql + " and a.fcabbarcode= :cabFequipmentbarcode "
        param["cabFequipmentbarcode"] = cabinet.cabFequipmentbarcode
    if cabinet.order == "desc":
        sql = sql + " order by  fusedate desc "
    else:
        sql = sql + " order by fusedate asc "
    sql = sql + " limit :limit offset :offset "
    param["limit"] = cabinet.limit
    param["offset"] = cabinet.offset
    data = db.execute(text(sql), param).fetchall()

    data_list = []
    for key in range(len(data)):
        d1 = {}
        d1['fusedate'] = data[key][0]
        d1['fsiteNo'] = data[key][1]
        d1['fsite_name'] = data[key][2]
        d1['fpointName'] = data[key][3]
        d1['fconname'] = data[key][4]
        d1['fcabname'] = data[key][5]
        d1['fboxname'] = data[key][6]
        d1['fbegtime'] = data[key][7]
        d1['fendtime'] = data[key][8]
        d1['fdictname'] = data[key][9]
        d1['fmessage'] = data[key][10]
        data_list.append(d1)

    info_dict = {"list": data_list, "limit": cabinet.limit, "page": cabinet.pageIndex,
                 "pages": (count + cabinet.limit - 1) // cabinet.limit,
                 "total": count}
    return BaseResponse.success(data=info_dict)
